/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tt.conn;

import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author PARDmx
 */
public class ConexionDBP {

    String url;
    String database;
    String user;
    String pwd;
    Connection con;
    String query;
    String field = "";
    String value = "";
    Statement st;
    ResultSet rs;
    ResultSetMetaData rsmd;

    public ConexionDBP() {
        //database = "umaaxcey_dehrs";
        //user = "umaaxcey_dehrs";
        database = "dehrs";
        user = "dehrs-admin";
        //pwd = "tt-b016";
        pwd = "ttb016";
        url = "jdbc:postgresql://localhost:5432/" + database;

    }

    public int conectarDB() {
        try {
            Class.forName("org.postgresql.Driver");
            con = DriverManager.getConnection(url, user, pwd);
            st = con.createStatement();
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return 1;
        }
    }

    public int desconectarDB() {
        try {
            con.close();
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return 1;
        }
    }

    public int updateAT(String table, String f, String v, String id) {


        query = "UPDATE " + table + " SET " + f + " ='" + v + "' WHERE id_user='" + id + "'";

        System.out.println(query);
        try {
            System.out.println(query);

            st.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
            return 1;
        }

        return 0;

    }

    public String insertDB(String table, ArrayList<String> fields, ArrayList<String> values) {

        field += fields.get(0).toString();
        value += "'" + values.get(0).toString() + "'";

        for (int i = 1; i < fields.size(); i++) {
            field += ", " + fields.get(i).toString();
            value += ", '" + values.get(i).toString() + "'";
        }

        if (!(field.equalsIgnoreCase("") && value.equalsIgnoreCase(""))) {

            query = "INSERT INTO " + table + " (" + field + ") VALUES (" + value + ")";


            //System.out.println(query);
            try {
                System.out.println(query);
                st.execute(query);
            } catch (SQLException e) {
                e.printStackTrace();
                return e.toString();
            }
            field = "";
            value = "";
            return "ok";
        } else {
            return "e";
        }
    }

    public ArrayList<String> seleccionar(String table, ArrayList<String> fields, String value) {
        ArrayList<String> res = new ArrayList<String>();
        int ncol;


        field += fields.get(0).toString();

        for (int i = 1; i < fields.size(); i++) {
            field += ", " + fields.get(i).toString();
        }

        query = "SELECT " + field + " FROM " + table;

        try {
            rs = st.executeQuery(query);
            rsmd = rs.getMetaData();
            ncol = rsmd.getColumnCount();

            while (rs.next()) {
                for (int i = 1; i <= ncol; i++) {
                    res.add(rs.getString(i));
                }
//                res.add(rs.getString(1));
//                res.add(rs.getString(2));
//                res.add(rs.getString(3));
//                res.add(rs.getString(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return res;
        }

        return res;
    }

    public ArrayList<String> buscar(String v, String r) {
        ArrayList<String> res = new ArrayList<String>();

        query = "select id_user,nombre from user_face where nombre like '" + v + "%' or usuario like '" + v + "%' and rol='" + r + "'";
        //query = "select id_user,nombre from user_face where nombre like 'l%' or usuario like 'l%' and rol='0'";

        try {
            rs = st.executeQuery(query);
            while (rs.next()) {
                res.add(rs.getString(1));
                res.add(rs.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return res;
        }

        return res;
    }

    public ArrayList<String> usuarioExiste(String id) {
        ArrayList<String> res = new ArrayList<String>();

        query = "SELECT * FROM user_face where id_user='" + id + "'";

        try {
            rs = st.executeQuery(query);
            while (rs.next()) {
                res.add(rs.getString("id_user"));
                res.add(rs.getString("rol"));
                res.add(rs.getString("correo"));
            }
            return res;
        } catch (SQLException e) {
            e.printStackTrace();
            return res;
        }
    }

    public ArrayList<String> publicacionExiste(String id) {
        ArrayList<String> res = new ArrayList<String>();

        query = "SELECT id_publicacion FROM publicacion where id_publicacion='" + id + "'";

        try {
            rs = st.executeQuery(query);
            while (rs.next()) {
                res.add(rs.getString("id_publicacion"));
            }
            return res;
        } catch (SQLException e) {
            e.printStackTrace();
            return res;
        }
    }

    public ArrayList<String> buscarPublicaciones(String id, String emo) {
        ArrayList<String> res = new ArrayList<String>();

        query = "SELECT p.texto, p.fecha FROM publicacion p WHERE p.id_user ='" + id + "'";
        try {
            rs = st.executeQuery(query);
            while (rs.next()) {
                res.add(rs.getString("texto"));
                res.add(rs.getString("fecha"));
            }
            return res;
        } catch (SQLException e) {
            e.printStackTrace();
            return res;
        }
    }
}
